package model.roomManagement.Implementation;
import java.io.File;

import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.*;   
import java.util.ArrayList;

import model.roomManagement.Interface.IDBC;
import model.roomManagement.Interface.IUser;




public class JDBCTest implements IDBC  {
	Connection conn ;
	
	 Object lock=new Object();//对象锁
	 static final String UPDATA_USER_ROOMLIST_SQL = "UPDATE  user SET roomIdList=? Where userId=?";
	 static final String UPDATA_ROOM_USERLIST_SQL = "UPDATE  room SET userIdList=? Where roomId=?";
	 static final String UPDATA_ROOM_SAVE_SQL = "UPDATE room SET save=? Where roomId=?";
	 
	 static final String UPDATA_USER_SQL = "UPDATE  user SET password=? Where userId=?";
	 

    static final String GET_USER_RL_SQL = "SELECT roomIdList FROM user WHERE userId = ?";
    static final String GET_ROOM_UL_SQL = "SELECT userIdList FROM room WHERE roomId = ?";
    static final String GET_ROOM_SAVE_SQL = "SELECT save FROM room WHERE roomId = ?";
    static final String CHECK_ROOM_INFO_SQL = "SELECT COUNT(*) FROM room WHERE roomId = ? and password=?";
    static final String CRAET_ROOM_SQL = "INSERT INTO room(ownerId, Subject,password) VALUES (?,?,?)";
    static final String GET_FILE_SQL="SELECT filePath FROM file WHERE room=? AND fileName = ? " ;
    static final String GET_FILE_LIST_SQL="SELECT fileName FROM file WHERE room=? " ;
    
    static final String INPUT_FILE_SQL = "INSERT INTO file(room,fileName,filePath,ownerId) VALUES (?,?,?,?)";
    static final String DELETE_FILE_SQL="DELETE FROM file WHERE room=? AND fileName = ? AND ownerId=? " ;
    static final String DELETE_ROOM_SQL="DELETE FROM room WHERE ownerId=?  AND roomId=? " ;
    static final String GET_USER_INFO_SQL="SELECT userId,name,Password FROM user WHERE userId = ? " ;
    static final String GET_ROOM_INFO_SQL="SELECT roomId,ownerId,Subject,userIdList FROM room WHERE roomId = ? " ;
    
    
	public void start(){
	
		String driver = "com.mysql.jdbc.Driver"; 
		
		String url = "jdbc:MySQL://127.0.0.1:3306/globalmeetingsystem";
		
		String user = "root"; 

		String password = "199191"; 
		try { 
		
			Class.forName(driver).newInstance();

			
			
			conn = DriverManager.getConnection(url, user, password); 
		

		} catch(ClassNotFoundException e) { 
	 
			e.printStackTrace();  
		} catch(SQLException e) { 
			e.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace(); 
		}
	}

public boolean userCheck(int userId, String password){
	boolean right=false;
	


	Statement statement;
	try {
		statement = conn.createStatement();
	
	
	String sql = "select *"+","+"count(*)"+" from user"+" where user.userId="+userId+" and user.password='"+password+"'";

	
	ResultSet rs = statement.executeQuery(sql); 


	String name = null;  
	int count=0;
	while(rs.next()) {
		name = rs.getString("password"); 
		if(name==null&&count==0) //�ж��Ƿ�Ϊ�� 
			break;
		count++;
		right=true;
 
	} 
	rs.close();


	} catch (SQLException e) {

		e.printStackTrace();
	} 
	

	return right;
}

@Override
public  boolean  addRtoU(int userId, int roomId) {

	 try {
     
		 ArrayList roomlist=getRLbyUserId(userId);

		 
		 
	 	 if(!roomlist.contains(roomId)){    //避免重复 
			  PreparedStatement pstmt = conn.prepareStatement(UPDATA_USER_ROOMLIST_SQL);
			    roomlist.add(roomId);
				pstmt.setObject(1,roomlist);
			    pstmt.setInt(2, userId);
				
				if(pstmt.executeUpdate()==0)
					return false;
				
			    pstmt.close();
			    
				 roomlist=getRLbyUserId(userId);

				 return true;
	        }
		
	
	 	 
	} catch (Exception e) {

		e.printStackTrace();
		return false;
	}
	 
	return false;
}

public synchronized ArrayList getULbyRoomId(int roomId) throws Exception{
	 PreparedStatement pstmt1 = conn.prepareStatement(GET_ROOM_UL_SQL);
	 pstmt1.setInt(1,roomId);
	 ResultSet rs = pstmt1.executeQuery();
	 rs.next();
	 InputStream is;
	 ObjectInputStream oips = null;
	 ArrayList userlist;

	 
	 if((is=rs.getBinaryStream(1))!=null){
	 oips = new ObjectInputStream(is);
	 userlist=(ArrayList)(oips.readObject());
	   oips.close();
	    rs.close();
	    pstmt1.close();
	 }
	 else{
		 userlist=new ArrayList();
	 }

	return userlist;
}


public synchronized ArrayList getRLbyUserId(int userId) throws Exception{
	 PreparedStatement pstmt1 = conn.prepareStatement(GET_USER_RL_SQL);
	 pstmt1.setInt(1,userId);
	 ResultSet rs = pstmt1.executeQuery();
	 rs.next();
	 InputStream is;
	 ObjectInputStream oips = null;
	 ArrayList roomlist;
	 

	 int ownerId=-1;
	 String subject=null;
	 
	 if((is=rs.getBinaryStream(1))!=null){
	 oips = new ObjectInputStream(is);
	 roomlist=(ArrayList)(oips.readObject());
	   oips.close();  
	    rs.close();
	    pstmt1.close();
	 }
	 else{
		 roomlist=new ArrayList();
	 }

	return roomlist;
}


public boolean addUtoR(int roomId, int userId) {
	
	
	
	 try {
   
	
		 ArrayList userlist=getULbyRoomId(roomId);

		 
		 if(!userlist.contains(userId)){    //	避免重复 
			  PreparedStatement pstmt = conn.prepareStatement(UPDATA_ROOM_USERLIST_SQL);
			  userlist.add(userId);
				pstmt.setObject(1,userlist);
			    pstmt.setInt(2, roomId);
				
				if(pstmt.executeUpdate()==0)
					return false;
				
		
			    pstmt.close();
			    
				userlist=getULbyRoomId(roomId);

				 
				 return true;
	        }

		
	} catch (Exception e) {

		e.printStackTrace();
		return false;
	}
	  
	  
	return false;
}




@Override
public int creatRoom(int userId, String subject, String password) {
	 

	
	int roomId = -1;
	try {
		PreparedStatement pstmt = conn.prepareStatement(CRAET_ROOM_SQL,Statement.RETURN_GENERATED_KEYS);
		pstmt.setInt(1,userId);
		pstmt.setString(2, subject);
		pstmt.setString(3,password );
	

		if(pstmt.executeUpdate()==0)
			return -1;
		
	 ResultSet rs = pstmt.getGeneratedKeys();

	
	    if (rs.next()) {
	    	//以 Java 编程语言中 int 的形式获取此 ResultSet 对象的当前行中指定列的值。
	    	roomId = rs.getInt(1);
	    	
	    	addUtoR(roomId,userId);
	    	addRtoU(userId,roomId);
	    }
	 
	 
	 
	} catch (SQLException e) {
	
		e.printStackTrace();
	}
	
	

	
	return roomId;
	
	
	
	
	
}

@Override
public boolean joinCheck(int roomId, String password) {

	boolean right=false;
	
	PreparedStatement pstmt;
	try {
		pstmt = conn.prepareStatement(CHECK_ROOM_INFO_SQL);

	pstmt.setInt(1,roomId);
	pstmt.setString(2, password );
	ResultSet rs =pstmt.executeQuery();
	rs.next();
	int count=rs.getInt(1);
	
	if(count==1){//只有一个符合条件
		right=true;
	
	}
	} catch (SQLException e) {

		e.printStackTrace();
	}
	

	
	return right;
}

@Override
public boolean deleteRofU(int userId, int roomId) {//删除用户的 房间列表 
	 
	 try {
    
		 ArrayList roomlist=getRLbyUserId(userId);

		 
	 	 if(roomlist.contains(roomId)){    //必须包含
			  PreparedStatement pstmt = conn.prepareStatement(UPDATA_USER_ROOMLIST_SQL);
			  roomlist.remove(Integer.valueOf(roomId));
				pstmt.setObject(1,roomlist);
			    pstmt.setInt(2, userId);
				
				if(pstmt.executeUpdate()==0)
					return false;
				
		
			    pstmt.close();
			    
	
	       
		
		 roomlist=getRLbyUserId(userId);

		 return true;
	 	 }
	 	 
	} catch (Exception e) {
	
		e.printStackTrace();
		return false;
	}
	
	return false;
}

@Override
public boolean deleteUofR(int roomId, int userId) {
	
	 try {
		   
			
		 ArrayList userlist=getULbyRoomId(roomId);

		 
		 
		 if(userlist.contains(userId)){    //必须包含
			  PreparedStatement pstmt = conn.prepareStatement(UPDATA_ROOM_USERLIST_SQL);
			  userlist.remove(Integer.valueOf(userId));
				pstmt.setObject(1,userlist);
			    pstmt.setInt(2, roomId);
				
				if(pstmt.executeUpdate()==0)
					return false;
				
			    pstmt.close();
			    
				userlist=getULbyRoomId(roomId);
		
				 return true;
	        }
		  //  conn.close();
		
	} catch (Exception e) {

		e.printStackTrace();
		return false;
	}
	
	return false;
}

@Override
public boolean addFile(File file, int room,int ownerId) {
     
   
    String fileName=file.getName();

    String filePath=file.getPath();
    
 	try {
		
     
        
		PreparedStatement pstmt = conn.prepareStatement(INPUT_FILE_SQL);
		//pstmt.setInt(1,userId);
		pstmt.setInt(1, room);
		pstmt.setString(2,fileName );
		pstmt.setString(3,filePath );
		pstmt.setInt(4,ownerId );
		
		
		if(pstmt.executeUpdate()==0)
			return false;
		


	 
	} catch (Exception e) {
	
		return false;
	}
	return true;
}

@Override
public File getFile(String fileName, int room) {
	File file=null;
	String filePath=null;
	try {
		PreparedStatement pstmt= conn.prepareStatement(GET_FILE_SQL);
	
	pstmt.setInt(1, room);
	pstmt.setString(2,fileName );
	

	if(!pstmt.execute())
		return null;
	
	ResultSet rs=pstmt.getResultSet();

	
	if(rs.next()){
		filePath=rs.getString(1);
	}
		
	
	} catch (SQLException e) {
		
		e.printStackTrace();
	}
	if(filePath!=null){
		file=new File(filePath);
	}
	
	return file;
}

@Override
public boolean deleteFile(String fileName, int room, int ownerId) {
	
	
	try {
		PreparedStatement pstmt= conn.prepareStatement(DELETE_FILE_SQL);
	
	pstmt.setInt(1, room);
	pstmt.setString(2,fileName );
	pstmt.setInt(3, ownerId);

	if(pstmt.executeUpdate()==0)
		return false;
	


	
	
	} catch (SQLException e) {
		
		e.printStackTrace();
		return false;
	}
	
	
	return true;
}

@Override
public boolean deleteRoom(int ownerId, int roomId) {
	

	try {
		ArrayList userList=getULbyRoomId(roomId);
		for(int i=0;i<userList.size();i++){
		  deleteRofU((Integer)userList.get(i),roomId);
		}
		
	
		PreparedStatement pstmt= conn.prepareStatement(DELETE_ROOM_SQL);
	
	pstmt.setInt(1, ownerId);
	pstmt.setInt(2, roomId);

	
	if(pstmt.executeUpdate()==0)
		return false;
	
	
	} catch (Exception e) {
		
		e.printStackTrace();
		return false;
	}
	
	
	return true;
}

@Override
public IUser getUserInfoByUId(int userId) throws Exception {
	
	String name=null;
	String password=null;
	int id =-1;

	ArrayList roomList=null;
	
	
	
	PreparedStatement pstmt=conn.prepareStatement(GET_USER_INFO_SQL);
	pstmt.setInt(1, userId);
	if(!pstmt.execute())
		return null;
	
	ResultSet rs=pstmt.getResultSet();
	
	
	if(rs.next()){
	id=rs.getInt(1);
	name=rs.getString(2);
	password=rs.getString(3);
		
	}
	roomList=getRLbyUserId(userId);
	
	return new User(name,password,id,roomList);
}

@Override
public synchronized String getRoomInfoByRId(int roomId) throws Exception {
	
	
	int rId =0;
	int ownerId=0;
    String subject=null;
	
	
	
	
	PreparedStatement pstmt=conn.prepareStatement(GET_ROOM_INFO_SQL);
	pstmt.setInt(1, roomId);
	if(!pstmt.execute())
		return null;
	ResultSet rs=pstmt.getResultSet();
	
	 InputStream is;
	 ObjectInputStream oips = null;
	 ArrayList userlist=null;

	
	
	if(rs.next()){
		rId=rs.getInt(1);
		ownerId=rs.getInt(2);
	subject=rs.getString(3);
	is=rs.getBinaryStream(4);
	 oips = new ObjectInputStream(is);
	 userlist=(ArrayList)(oips.readObject());
	 
		
	}
	
	
	return rId+"/"+ownerId+"/"+subject+"/"+userlist;
}

@Override
public ArrayList<String> getFileNameList(int roomId) throws SQLException {
	// TODO Auto-generated method stub
	ArrayList fileList=new ArrayList();
	

	String fileName=null;
	PreparedStatement pstmt=conn.prepareStatement(GET_FILE_LIST_SQL);
	pstmt.setInt(1, roomId);
	if(!pstmt.execute())
		return null;
	
	ResultSet rs=pstmt.getResultSet();
	while(rs.next()){
		fileName=rs.getString(1);
		fileList.add(fileName);
		
	}
	

	return fileList;
}

@Override
public boolean changePassWord(int userId, String oldPassword, String newPassword) {
	if(userCheck(userId,oldPassword)){
		   try {
		  PreparedStatement pstmt = conn.prepareStatement(UPDATA_USER_SQL);
	
		  pstmt.setString(1,newPassword);
		 
				pstmt.setInt(2, userId);
		
				
				if(pstmt.executeUpdate()==0)
					return false;
				
				
		    pstmt.close();
			} catch (SQLException e) {
			return false;
			}
		
		return true;
	}

	
	
	return false;
}

@Override
public synchronized Object getRoom(int roomId) {
	Object room=null;
	
	try{
	 PreparedStatement pstmt = conn.prepareStatement(GET_ROOM_SAVE_SQL);
	 pstmt.setInt(1,roomId);
	 ResultSet rs = pstmt.executeQuery();
	 rs.next();
	 InputStream is;
	 ObjectInputStream oips = null;
      
	 if((is=rs.getBinaryStream(1))!=null){
	 oips = new ObjectInputStream(is);
	  room=oips.readObject();
	   oips.close();
	    rs.close();
	    pstmt.close();
	 }
	}catch(Exception e){
		e.printStackTrace();
		return room;
	}

return room;
}

@Override
public boolean saveRoom(Object room, int roomId) {
	


	try{
		  PreparedStatement pstmt = conn.prepareStatement(UPDATA_ROOM_SAVE_SQL);

			pstmt.setObject(1,room);
		    pstmt.setInt(2, roomId);
			
			if(pstmt.executeUpdate()==0)
				return false;
			
	
		    pstmt.close();
		    


			 return true;
      
	
	} catch (Exception e) {

	e.printStackTrace();
	return false;
  }

}
		

		
	}   
